/*
Checks if the user is in any MasterAdmin CAG appart from the one passed in.  
If not the user will be removed from the Content Administrator role.
*/
CREATE PROC [dbo].[asi_ContentAdministratorCheck]
   @contactKey uniqueidentifier,
   @groupKey uniqueidentifier
AS
BEGIN

    DECLARE @count int
    DECLARE @cagRoleKey uniqueidentifier

    SELECT @count = COUNT(*)
      FROM [dbo].[GroupMemberDetail] gmd INNER JOIN [dbo].[GroupMember] gm ON gmd.[GroupMemberKey] = gm.[GroupMemberKey]
            INNER JOIN [dbo].[ContentManagerAuthorityGroup] cmag ON gmd.[GroupKey] = cmag.[GroupKey]
     WHERE cmag.[IsMasterAdmin] = 1
       AND gm.[MemberContactKey] = @contactKey
       AND gmd.[GroupKey] <> @groupKey

    IF @count = 0
    BEGIN

        -- Remove the user from the Content Administrator role
        SELECT @cagRoleKey = [RoleKey] FROM [dbo].[RoleMain] WHERE [Name] = 'Content Administrator'

        DELETE FROM [dbo].[UserRole]
        WHERE [UserKey] = @contactKey
          AND [RoleKey] = @cagRoleKey

    END


END

GO